package DataBase;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


public class UserInfoManager {
	public static DbcpBean dbcp ;
	static{
		dbcp = new DbcpBean();
	}
	public static int insert(UserInfo user) {
//		Connection conn = PoolManager.getConnection();
//		int result = 0;
//		if(conn != null){
//			try {
//				Statement stmt = conn.createStatement();
//				String sql = "insert into tb_UserInfo (user_name,user_pwd,user_realname,user_sex,"
//					+"user_tel,user_email,user_address) values ('"
//					+ user.getUser_name() + "' , '" 
//					+ user.getUser_pwd() + "' , '"
//					+ user.getUser_realname() + "' , '"
//					+ user.getUser_sex() + "' , '"
//					+ user.getUser_tel() + "' , '"
//					+ user.getUser_email() + "' , '"
//					+ user.getUser_address() + "');";
//				result  = stmt.executeUpdate(sql);
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}			
//		}
//		PoolManager.freeConnection(conn);   
//	    return result;
//	    
//		DbcpBean dbcp = new DbcpBean();
		Connection conn = dbcp.getConnection();
		int result = 0;
		if(conn != null){
			try {
				Statement stmt = conn.createStatement();
				String sql = "insert into tb_UserInfo (user_name,user_pwd,user_realname,user_sex,"
					+"user_tel,user_email,user_address) values ('"
					+ user.getUser_name() + "' , '" 
					+ user.getUser_pwd() + "' , '"
					+ user.getUser_realname() + "' , '"
					+ user.getUser_sex() + "' , '"
					+ user.getUser_tel() + "' , '"
					+ user.getUser_email() + "' , '"
					+ user.getUser_address() + "');";
				result  = stmt.executeUpdate(sql);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		}
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		PoolManager.freeConnection(conn);   
	    return result;
	}
	
	public static int delete(String user_name) throws SQLException{
		// TODO Auto-generated method stub
//		DbcpBean dbcp = new DbcpBean();
		Connection conn = dbcp.getConnection();
		int result = 0;
		String sql = null;
		if(conn != null){
			try{
				Statement stmt = conn.createStatement();
				sql = "delete from tb_userinfo where user_name ='"
					+user_name + "';"; 
				result = stmt.executeUpdate(sql);
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
			
		}
		conn.close();
		return result;
	}
	
	public static int update(UserInfo users) {
//		DbcpBean dbcp = new DbcpBean();
		Connection conn = dbcp.getConnection();
		int result = 0;
		String sql = null;
		if(conn != null){
			try{
				Statement stmt = conn.createStatement();
				sql = "update tb_userinfo set user_name = '" + users.getUser_name() + "' , " 
				                         +  " user_pwd  = '" + users.getUser_pwd()  + "' , " 
				                         +  " user_realname = '" + users.getUser_realname() + "' , " 
				                         +  " user_sex = '" + users.getUser_sex() + "' , " 
				                         +  " user_tel = '" + users.getUser_tel() + "' , " 
				                         +  " user_email= '" + users.getUser_email() + "' , " 
				                         +  " user_address = '" + users.getUser_address() + "'"  
				                         +  " where " +"user_ID = " + users.getUser_ID() + " ; "; 
				result =  stmt.executeUpdate(sql);
			}catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}			
		}
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	    return result;
	}
	
	public static ArrayList<UserInfo> select(String user_name) {
//		DbcpBean dbcp = new DbcpBean();
		Connection conn = dbcp.getConnection();
		ResultSet result = null;
		ArrayList<UserInfo> users = new ArrayList<UserInfo>();
		if(conn != null){
			try {
				Statement stmt = conn.createStatement();
				String sql = null;
				if(user_name == ""||user_name == "*"){
					sql = "select * from tb_UserInfo";
				}
				else{
					sql = "select * from tb_UserInfo where user_name = '"
						+ user_name +"';";
				}
				result  = stmt.executeQuery(sql);
				while(result.next()){
				    UserInfo user = new UserInfo();
					user.setUser_ID(result.getInt("user_ID"));
					user.setRole_ID(result.getInt("role_ID"));
					user.setUser_name(result.getString("user_name"));
					user.setUser_pwd(result.getString("user_pwd"));
					user.setUser_realname(result.getString("user_realname"));
					user.setUser_sex(result.getString("user_sex"));
					user.setUser_tel(result.getString("user_tel"));
					user.setUser_email(result.getString("user_email"));
					user.setUser_address(result.getString("user_address"));
					users.add(user);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}			
		}
		try {
			conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return users;
	}
	
	
	
	
	
	public static void main(String[] args) throws  SQLException {   
		/*UserInfo user = new UserInfo();
		user.setUser_name("user");
		user.setUser_pwd("123");
		user.setUser_sex("2");
		if(UserInfoManager.insert(user) == 0){
			System.out.println("���ʧ��");
		}
		System.out.println(UserInfoManager.select("user_name"));*/   //����
		
		
		/*String user_name = "user";
	    UserInfoManager.delete(user_name);*/    //ɾ��
		
		/*String user_name = "user";
		System.out.println(UserInfoManager.select(user_name));*/  //��ѯ
		String name = "烨陈魁";
//		try {
//		String test=new String(name.getBytes("ISO-8859-1"),"UTF-8");
		UserInfo ins = new UserInfo();
		
//		String test=new String(name.getBytes("ISO-8859-1"),"UTF-8");
		ins.setUser_name(name);
		ins.setUser_sex("m");
		UserInfoManager.insert(ins);
//		UserInfo user = (UserInfoManager.select(name)).get(0);
//		System.out.println(user);
//		System.out.println(UserInfoManager.select(""));
//		} catch (UnsupportedEncodingException e) {
//		// TODO Auto-generated catch block
//		e.printStackTrace();
//		}

//		user.setUser_name("xmx");
//      UserInfoManager.update(user);
		
	}


}
